# MySQL配置
MYSQL_PASSWORD = "123456"  # 将这里替换为实际密码
import pymysql
from sqlalchemy import create_engine, Column, Integer, String, Float, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建基类
Base = declarative_base()

# 定义股票数据表
class StockTable(Base):
    __tablename__ = 'stocks'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    stock_code = Column(String(20), nullable=False)  # 股票代码
    stock_name = Column(String(50), nullable=False)  # 股票名称
    latest_price = Column(Float)  # 最新报价
    change_percent = Column(Float)  # 涨跌幅
    change_amount = Column(Float)  # 涨跌额
    volume = Column(String(50))  # 成交量
    turnover = Column(String(50))  # 成交额
    amplitude = Column(Float)  # 振幅
    high = Column(Float)  # 最高
    low = Column(Float)  # 最低
    open_price = Column(Float)  # 今开
    prev_close = Column(Float)  # 昨收
    timestamp = Column(String(20))  # 时间戳

class MySQLPipeline:
    """MySQL数据库存储管道"""
    
    def __init__(self):
        self.engine = None
        self.Session = None

    def open_spider(self, spider):
        """爬虫启动时调用"""
        try:
            # 连接MySQL数据库（先不使用密码，后面再配置）
            self.engine = create_engine(f'mysql+pymysql://root:{MYSQL_PASSWORD}@localhost:3306/stock_db?charset=utf8mb4')
            # 创建表
            Base.metadata.create_all(self.engine)
            self.Session = sessionmaker(bind=self.engine)
            spider.logger.info("成功连接到MySQL数据库")
        except Exception as e:
            spider.logger.error(f"连接数据库失败: {e}")

    def close_spider(self, spider):
        """爬虫关闭时调用"""
        if self.engine:
            self.engine.dispose()

    def process_item(self, item, spider):
        """处理每个数据项"""
        try:
            session = self.Session()
            
            # 创建新记录
            stock_record = StockTable(
                stock_code=item['stock_code'],
                stock_name=item['stock_name'],
                latest_price=item['latest_price'],
                change_percent=item['change_percent'],
                change_amount=item['change_amount'],
                volume=item['volume'],
                turnover=item['turnover'],
                amplitude=item['amplitude'],
                high=item['high'],
                low=item['low'],
                open_price=item['open_price'],
                prev_close=item['prev_close'],
                timestamp=item['timestamp']
            )
            
            # 添加到会话并提交
            session.add(stock_record)
            session.commit()
            
            spider.logger.info(f"成功保存股票: {item['stock_name']} ({item['stock_code']})")
            
        except Exception as e:
            session.rollback()
            spider.logger.error(f"保存股票数据失败: {e}")
        finally:
            session.close()
        
        return item

class ConsolePipeline:
    """控制台输出管道"""
    
    def process_item(self, item, spider):
        """在控制台显示股票信息"""
        print(f"\n=== 股票信息 #{item['id']} ===")
        print(f"股票代码: {item['stock_code']}")
        print(f"股票名称: {item['stock_name']}")
        print(f"最新报价: {item['latest_price']}")
        print(f"涨跌幅: {item['change_percent']}%")
        print(f"涨跌额: {item['change_amount']}")
        print(f"成交量: {item['volume']}")
        print(f"成交额: {item['turnover']}")
        print(f"振幅: {item['amplitude']}%")
        print(f"最高: {item['high']}")
        print(f"最低: {item['low']}")
        print(f"今开: {item['open_price']}")
        print(f"昨收: {item['prev_close']}")
        print(f"更新时间: {item['timestamp']}")
        print("=" * 40)
        
        return item